using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Linq;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace InfoSite.Data
{
    public partial class SiteMenuItem
    {
        public SiteMenuItem()
        {
            Childs = new List<SiteMenuItem>();
        }

        #region Constants

        private const string TableName = "[dbo].[SiteMenuItem]";

        #endregion

        #region Properties

        public Int32? Id { get; set; }
        public Int32? ParentSiteMenuItemId { get; set; }
        public Int32? Location { get; set; }
        public String Title { get; set; }
        public String Url { get; set; }
        public Int32? CreatedBy { get; set; }
        public DateTime? CreatedDateTime { get; set; }
        public Int32? LastModifiedBy { get; set; }
        public DateTime? LastModifiedDateTime { get; set; }
        public Int32? DeletedBy { get; set; }
        public DateTime? DeletedDateTime { get; set; }
        public Boolean? IsAdmin { get; set; }
        public List<SiteMenuItem> Childs { get; set; }

        #endregion

        #region Methods

        #region Mapping Methods

        protected void MapTo(DataSet ds)
        {
            if (ds == null)
                ds = new DataSet();

            if (ds.Tables["TableName"] == null)
                ds.Tables.Add(TableName);

            ds.Tables[TableName].Columns.Add("Id", typeof(Int32));
            ds.Tables[TableName].Columns.Add("ParentSiteMenuItemId", typeof(Int32));
            ds.Tables[TableName].Columns.Add("Location", typeof(Int32));
            ds.Tables[TableName].Columns.Add("Title", typeof(String));
            ds.Tables[TableName].Columns.Add("Url", typeof(String));
            ds.Tables[TableName].Columns.Add("CreatedBy", typeof(Int32));
            ds.Tables[TableName].Columns.Add("CreatedDateTime", typeof(DateTime));
            ds.Tables[TableName].Columns.Add("LastModifiedBy", typeof(Int32));
            ds.Tables[TableName].Columns.Add("LastModifiedDateTime", typeof(DateTime));
            ds.Tables[TableName].Columns.Add("DeletedBy", typeof(Int32));
            ds.Tables[TableName].Columns.Add("DeletedDateTime", typeof(DateTime));
            ds.Tables[TableName].Columns.Add("IsAdmin", typeof(Boolean));

            DataRow dr = ds.Tables[TableName].NewRow();

            dr["Id"] = Id ?? (object)DBNull.Value;
            dr["ParentSiteMenuItemId"] = ParentSiteMenuItemId ?? (object)DBNull.Value;
            dr["Location"] = Location ?? (object)DBNull.Value;
            dr["Title"] = Title ?? (object)DBNull.Value;
            dr["Url"] = Url ?? (object)DBNull.Value;
            dr["CreatedBy"] = CreatedBy ?? (object)DBNull.Value;
            dr["CreatedDateTime"] = CreatedDateTime ?? (object)DBNull.Value;
            dr["LastModifiedBy"] = LastModifiedBy ?? (object)DBNull.Value;
            dr["LastModifiedDateTime"] = LastModifiedDateTime ?? (object)DBNull.Value;
            dr["DeletedBy"] = DeletedBy ?? (object)DBNull.Value;
            dr["DeletedDateTime"] = DeletedDateTime ?? (object)DBNull.Value;
            dr["IsAdmin"] = IsAdmin ?? (object)DBNull.Value;

            ds.Tables[TableName].Rows.Add(dr);

        }

        protected void MapFrom(DataRow dr)
        {
            Id = dr["Id"] != DBNull.Value ? Convert.ToInt32(dr["Id"]) : Id = null;
            ParentSiteMenuItemId = dr["ParentSiteMenuItemId"] != DBNull.Value ? Convert.ToInt32(dr["ParentSiteMenuItemId"]) : ParentSiteMenuItemId = null;
            Location = dr["Location"] != DBNull.Value ? Convert.ToInt32(dr["Location"]) : Location = null;
            Title = dr["Title"] != DBNull.Value ? Convert.ToString(dr["Title"]) : Title = null;
            Url = dr["Url"] != DBNull.Value ? Convert.ToString(dr["Url"]) : Url = null;
            CreatedBy = dr["CreatedBy"] != DBNull.Value ? Convert.ToInt32(dr["CreatedBy"]) : CreatedBy = null;
            CreatedDateTime = dr["CreatedDateTime"] != DBNull.Value ? Convert.ToDateTime(dr["CreatedDateTime"]) : CreatedDateTime = null;
            LastModifiedBy = dr["LastModifiedBy"] != DBNull.Value ? Convert.ToInt32(dr["LastModifiedBy"]) : LastModifiedBy = null;
            LastModifiedDateTime = dr["LastModifiedDateTime"] != DBNull.Value ? Convert.ToDateTime(dr["LastModifiedDateTime"]) : LastModifiedDateTime = null;
            DeletedBy = dr["DeletedBy"] != DBNull.Value ? Convert.ToInt32(dr["DeletedBy"]) : DeletedBy = null;
            DeletedDateTime = dr["DeletedDateTime"] != DBNull.Value ? Convert.ToDateTime(dr["DeletedDateTime"]) : DeletedDateTime = null;
            IsAdmin = dr["IsAdmin"] != DBNull.Value ? Convert.ToBoolean(dr["IsAdmin"]) : IsAdmin = null;
        }

        public static List<SiteMenuItem> MapFrom(DataSet ds)
        {
            // Initialise Collection.
            List<SiteMenuItem> objects = new List<SiteMenuItem>();

            // Validation.
            if (ds == null)
                throw new ApplicationException("Cannot map to dataset null.");

            if (ds.Tables[TableName].Rows.Count == 0)
                return objects;

            if (ds.Tables[TableName] == null)
                throw new ApplicationException("Cannot find table [dbo].[SiteMenuItem] in DataSet.");

            if (ds.Tables[TableName].Rows.Count < 1)
                throw new ApplicationException("Table [dbo].[SiteMenuItem] is empty.");

            // Map DataSet to Instance.
            foreach (DataRow dr in ds.Tables[TableName].Rows)
            {
                SiteMenuItem instance = new SiteMenuItem();
                instance.MapFrom(dr);
                objects.Add(instance);
            }

            // Return collection.
            return objects;
        }


        #endregion

        #region CRUD Methods

        [DataObjectMethod(DataObjectMethodType.Select, false)]
        public static SiteMenuItem Get(Int32 id)
        {
            SiteMenuItem instance = new SiteMenuItem();

            Database db = DatabaseFactory.CreateDatabase();
            const string sqlCommand = "[dbo].gspSiteMenuItem_SELECT";
            using (DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand, id))
            {
                using (DataSet ds = db.ExecuteDataSet(dbCommand))
                {
                    if (ds == null || ds.Tables[0].Rows.Count == 0) throw new ApplicationException(string.Format("Could not get SiteMenuItem ID:{0} from Database.", id));
                    // Return results.
                    ds.Tables[0].TableName = TableName;

                    instance.MapFrom(ds.Tables[0].Rows[0]);
                }
            }
            return instance;
        }

        #region INSERT
        private void Insert(Int32? parentSiteMenuItemId, Int32? location, String title, String url, Int32? createdBy, DateTime? createdDateTime, Int32? lastModifiedBy, DateTime? lastModifiedDateTime, Int32? deletedBy, DateTime? deletedDateTime, DbTransaction transaction = null)
        {
            Database db = DatabaseFactory.CreateDatabase();
            const string sqlCommand = "[dbo].gspSiteMenuItem_INSERT";
            using (DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand, parentSiteMenuItemId, location, title, url, createdBy, createdDateTime, lastModifiedBy, lastModifiedDateTime, deletedBy, deletedDateTime))
            {
                Id = Convert.ToInt32(transaction == null ? db.ExecuteScalar(dbCommand) : db.ExecuteScalar(dbCommand, transaction));
            }
        }

        /// <summary>
        /// Insert current SiteMenuItem to database.
        /// </summary>
        /// <param name="transaction">optional SQL Transaction</param>
        private void Insert(DbTransaction transaction = null)
        {
            Insert(ParentSiteMenuItemId, Location, Title, Url, CreatedBy, CreatedDateTime, LastModifiedBy, LastModifiedDateTime, DeletedBy, DeletedDateTime, transaction);
        }

        #endregion


        #region UPDATE
        private static void Update(Int32? id, Int32? parentSiteMenuItemId, Int32? location, String title, String url, Int32? createdBy, DateTime? createdDateTime, Int32? lastModifiedBy, DateTime? lastModifiedDateTime, Int32? deletedBy, DateTime? deletedDateTime, DbTransaction transaction = null)
        {
            Database db = DatabaseFactory.CreateDatabase();
            const string sqlCommand = "[dbo].gspSiteMenuItem_UPDATE";
            using (DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand))
            {
                db.DiscoverParameters(dbCommand);
                dbCommand.Parameters["@id"].Value = id;
                dbCommand.Parameters["@parentSiteMenuItemId"].Value = parentSiteMenuItemId;
                dbCommand.Parameters["@location"].Value = location;
                dbCommand.Parameters["@title"].Value = title;
                dbCommand.Parameters["@url"].Value = url;
                dbCommand.Parameters["@createdBy"].Value = createdBy;
                dbCommand.Parameters["@createdDateTime"].Value = createdDateTime;
                dbCommand.Parameters["@lastModifiedBy"].Value = lastModifiedBy;
                dbCommand.Parameters["@lastModifiedDateTime"].Value = lastModifiedDateTime;
                dbCommand.Parameters["@deletedBy"].Value = deletedBy;
                dbCommand.Parameters["@deletedDateTime"].Value = deletedDateTime;

                if (transaction == null)
                    db.ExecuteNonQuery(dbCommand);
                else
                    db.ExecuteNonQuery(dbCommand, transaction);
            }
        }

        private static void Update(SiteMenuItem SiteMenuItem)
        {
            SiteMenuItem.Update();
        }

        private static void Update(SiteMenuItem SiteMenuItem, DbTransaction transaction)
        {
            SiteMenuItem.Update(transaction);
        }

        /// <summary>
        /// Updates changes to the database.
        /// </summary>
        /// <param name="transaction">optional SQL Transaction</param>
        private void Update(DbTransaction transaction = (DbTransaction)null)
        {
            Database db = DatabaseFactory.CreateDatabase();
            const string sqlCommand = "[dbo].gspSiteMenuItem_UPDATE";
            DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
            db.DiscoverParameters(dbCommand);
            dbCommand.Parameters["@id"].SourceColumn = "Id";
            dbCommand.Parameters["@parentSiteMenuItemId"].SourceColumn = "ParentSiteMenuItemId";
            dbCommand.Parameters["@location"].SourceColumn = "Location";
            dbCommand.Parameters["@title"].SourceColumn = "Title";
            dbCommand.Parameters["@url"].SourceColumn = "Url";
            dbCommand.Parameters["@createdBy"].SourceColumn = "CreatedBy";
            dbCommand.Parameters["@createdDateTime"].SourceColumn = "CreatedDateTime";
            dbCommand.Parameters["@lastModifiedBy"].SourceColumn = "LastModifiedBy";
            dbCommand.Parameters["@lastModifiedDateTime"].SourceColumn = "LastModifiedDateTime";
            dbCommand.Parameters["@deletedBy"].SourceColumn = "DeletedBy";
            dbCommand.Parameters["@deletedDateTime"].SourceColumn = "DeletedDateTime";

            using (DataSet ds = new DataSet())
            {
                MapTo(ds);
                ds.AcceptChanges();
                ds.Tables[0].Rows[0].SetModified();
                if (transaction == null)
                    db.UpdateDataSet(ds, TableName, null, dbCommand, null, UpdateBehavior.Standard);
                else
                    db.UpdateDataSet(ds, TableName, null, dbCommand, null, transaction);
            }
        }

        #endregion


        #region DELETE
        [DataObjectMethod(DataObjectMethodType.Delete, false)]
        private static void Delete(Int32? id, DbTransaction transaction = null)
        {
            Database db = DatabaseFactory.CreateDatabase();
            const string sqlCommand = "[dbo].gspSiteMenuItem_DELETE";
            DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand, id);

            // Execute.
            if (transaction != null)
            {
                db.ExecuteNonQuery(dbCommand, transaction);
            }
            else
            {
                db.ExecuteNonQuery(dbCommand);
            }
        }

        /// <summary>
        /// Delete current SiteMenuItem from database.
        /// </summary>
        /// <param name="transaction">optional SQL Transaction</param>
        private void Delete(DbTransaction transaction = (DbTransaction)null)
        {
            Database db = DatabaseFactory.CreateDatabase();
            const string sqlCommand = "[dbo].gspSiteMenuItem_DELETE";
            using (DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand, Id))
            {
                if (transaction != null)
                {
                    db.ExecuteNonQuery(dbCommand, transaction);
                }
                else
                {
                    db.ExecuteNonQuery(dbCommand);
                }
            }
            Id = null;
        }

        #endregion


        #region SEARCH
        [DataObjectMethod(DataObjectMethodType.Select, false)]
        private static List<SiteMenuItem> Search(Int32? id = null, Int32? parentSiteMenuItemId = null, Int32? location = null, String title = null, String url = null, Int32? createdBy = null, DateTime? createdDateTime = null, Int32? lastModifiedBy = null, DateTime? lastModifiedDateTime = null, Int32? deletedBy = null, DateTime? deletedDateTime = null, Boolean? isAdmin = null)
        {
            Database db = DatabaseFactory.CreateDatabase();
            const string sqlCommand = "[dbo].gspSiteMenuItem_SEARCH";
            using (DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand, id, parentSiteMenuItemId, location, title, url, createdBy, createdDateTime, lastModifiedBy, lastModifiedDateTime, deletedBy, deletedDateTime, isAdmin))
            {
                using (DataSet ds = db.ExecuteDataSet(dbCommand))
                {
                    ds.Tables[0].TableName = TableName;
                    return MapFrom(ds);
                }
            }
        }


        private static List<SiteMenuItem> Search(SiteMenuItem searchObject)
        {
            return Search(searchObject.Id, searchObject.ParentSiteMenuItemId, searchObject.Location, searchObject.Title, searchObject.Url, searchObject.CreatedBy, searchObject.CreatedDateTime, searchObject.LastModifiedBy, searchObject.LastModifiedDateTime, searchObject.DeletedBy, searchObject.DeletedDateTime, searchObject.IsAdmin);
        }

        #endregion


        #endregion


        #endregion
    }
}